package tools;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class JdbcUtil {
	private static String url = "jdbc:postgresql://localhost:5432/mydb";
	private static String username = "postgres";
	private static String password = "123456";
	private JdbcUtil(){}
	public static Connection getConn() throws SQLException{
		return DriverManager.getConnection(url,username,password);
	}
	public static void close(Connection conn){
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void main(String[] args) throws SQLException {
		System.out.println(getConn());
	}

	/**
	 * 执行数据库插入操作
	 *
	 * @param valueMap  插入数据表中key为列名和value为列对应的值的Map对象
	 * @param tableName 要插入的数据库的表名
	 * @return 影响的行数
	 * @throws SQLException SQL异常
	 */
	public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException {

		/**获取数据库插入的Map的键值对的值**/
		Set<String> keySet = valueMap.keySet();
		Iterator<String> iterator = keySet.iterator();
		/**要插入的字段sql，其实就是用key拼起来的**/
		StringBuilder columnSql = new StringBuilder();
		/**要插入的字段值，其实就是？**/
		StringBuilder unknownMarkSql = new StringBuilder();
		Object[] bindArgs = new Object[valueMap.size()];
		int i = 0;
		while (iterator.hasNext()) {
			String key = iterator.next();
			columnSql.append(i == 0 ? "" : ",");
			columnSql.append(key);

			unknownMarkSql.append(i == 0 ? "" : ",");
			unknownMarkSql.append("?");
			bindArgs[i] = valueMap.get(key);
			i++;
		}
		/**开始拼插入的sql语句**/
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append(" (");
		sql.append(columnSql);
		sql.append(" )  VALUES (");
		sql.append(unknownMarkSql);
		sql.append(" )");
		return executeUpdate(sql.toString(), bindArgs);
	}

	public static int batchInsert(String tableName, List<String> cloumns,  List<Map<String, Object>> data) throws SQLException {


		/**要插入的字段sql，其实就是用key拼起来的**/
		StringBuilder columnSql = new StringBuilder();


		/**要插入的字段值，其实就是？**/
		StringBuilder unknownMarkSql = new StringBuilder();


		int i = 0;
		for (String cloumn : cloumns) {
			columnSql.append(i == 0 ? "" : ",");
			columnSql.append(cloumn);
			i++;
		}

		Object[] bindArgs = new Object[data.size()*data.get(0).size()];


		int arr = 0;
		for (Map<String, Object> valueMap : data) {
			System.out.println(valueMap);
			/**获取数据库插入的Map的键值对的值**/
			Set<String> keySet = valueMap.keySet();
			Iterator<String> iterator = keySet.iterator();
			int j = 0;

			unknownMarkSql.append(" (");
			while (iterator.hasNext()) {
				String key = iterator.next();
				unknownMarkSql.append(j == 0 ? "" : ",");
				unknownMarkSql.append("?");
				bindArgs[arr] = valueMap.get(key);
				j++;
				arr++;
				System.out.println(unknownMarkSql.toString());
			}
			unknownMarkSql.append(" ),");
		}



		/**开始拼插入的sql语句**/
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append(" (");
		sql.append(columnSql);
		sql.append(" )  VALUES ");
		sql.append(unknownMarkSql);
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" ");
		System.out.println(sql.toString());
		return executeUpdate(sql.toString(), bindArgs);
	}


	/**
	 * 可以执行新增，修改，删除
	 *
	 * @param sql      sql语句
	 * @param bindArgs 绑定参数
	 * @return 影响的行数
	 * @throws SQLException SQL异常
	 */
	public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException {
		/**影响的行数**/
		int affectRowCount = -1;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/**从数据库连接池中获取数据库连接**/
			connection = getConn();
			/**执行SQL预编译**/
			preparedStatement = connection.prepareStatement(sql.toString());
			/**设置不自动提交，以便于在出现异常的时候数据库回滚**/
			connection.setAutoCommit(false);
			System.out.println(getExecSQL(sql, bindArgs));
			if (bindArgs != null) {
				/**绑定参数设置sql占位符中的值**/
				for (int i = 0; i < bindArgs.length; i++) {
					preparedStatement.setObject(i + 1, bindArgs[i]);
				}
			}
			/**执行sql**/
			affectRowCount = preparedStatement.executeUpdate();
			connection.commit();
			String operate;
			if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
				operate = "删除";
			} else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
				operate = "新增";
			} else {
				operate = "修改";
			}
			System.out.println("成功" + operate + "了" + affectRowCount + "行");
			System.out.println();
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return affectRowCount;
	}

	private static String getExecSQL(String sql, Object[] bindArgs) {
		StringBuilder sb = new StringBuilder(sql);
		if (bindArgs != null && bindArgs.length > 0) {
			int index = 0;
			for (int i = 0; i < bindArgs.length; i++) {
				index = sb.indexOf("?", index);
				System.out.println(String.valueOf(bindArgs[i]));
				sb.replace(index, index + 1, String.valueOf(bindArgs[i]));
			}
		}
		return sb.toString();
	}
}
